﻿Imports System.Data.OleDb
Imports System.Net
Imports System.IO
Public Class frmSendSMS

#Region "NotifyIcon"

    Private Sub frmSendSMS_Resize(sender As Object, e As EventArgs) Handles Me.Resize
        Try
            If Me.WindowState = FormWindowState.Minimized Then
                Me.Visible = False
                NotifyIcon.Visible = True
                NotifyIcon.ShowBalloonTip(1, "SkyCoffee", "SkyCoffee is Still Running!", ToolTipIcon.Info)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub NotifyIcon_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles NotifyIcon.MouseDoubleClick
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuLaunchApplication_Click(sender As Object, e As EventArgs) Handles mnuLaunchApplication.Click
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuExitApplication_Click(sender As Object, e As EventArgs) Handles mnuExitApplication.Click
        Application.Exit()
    End Sub
#End Region

    Dim dsSMS As New DataSet()
    Dim intCurrentIndexSMS As Integer = 0
    Dim daSMS As New OleDbDataAdapter()
    Dim connSMS As New OleDbConnection()

    Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

    Dim dsStaff As New DataSet()
    Dim intCurrentIndexStaff As Integer = 0
    Dim daStaff As New OleDbDataAdapter()
    Dim connStaff As New OleDbConnection()
    Public Sub FillFields()
        txtID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldDataID").ToString()
        txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldDataFirstName").ToString()
        txtSurname.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldDataSurname").ToString()
        txtMobile.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldDataMobile").ToString()
    End Sub
    Public Sub FillFieldsStaff()
        txtID.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldDataID").ToString()
        txtFirstName.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldDataFirstName").ToString()
        txtSurname.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldDataSurname").ToString()
        txtMobile.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldDataMobile").ToString()
    End Sub
    Private Sub frmSendSMS_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Loads frmMenu
        pnlIcons.Controls.Clear()
        pnlIcons.Controls.Add(frmMenu.pnlIcons)

        'Load ToolTips
        AdminToolTips()

        'Generate Random Reference Number
        Dim intIDLength As Integer  'length of ID
        Dim intCount As Integer     'variable to count to the value of intIDLength
        Dim intAsc As Integer       'random number to choose what the character will be

        Dim strResult As String     'value of the resulting ID

        Dim intNumber As Integer
        Dim intCharacter As Integer 'the resulting character from one cycle

        intIDLength = 6             'length of SMS id

        Randomize()

        For intNumber = 1 To 1

            'number of random characters to generate
            For intCount = 1 To intIDLength


                'choose random character type to generate numbers
                intAsc = Int((1 * Rnd()) + 1)

                Select Case intAsc

                    Case 1
                        intCharacter = Int((Asc("9") - Asc("0") + 1) * Rnd() + Asc("0"))

                End Select

                'concatenate the resulting character to the previously generated character
                strResult = strResult & Chr(intCharacter)

                'repeat process if the value of "intCount" has not reached "intNumberDigits"
            Next intCount

            strResult = strResult

        Next intNumber

        'display the resulting id in the label
        txtSMSID.Text = strResult

        cmbSelectGroup.SelectedIndex = -1

        'Add to database
        connSMS.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        daSMS.SelectCommand = New OleDbCommand("SELECT * FROM tblSMS")
        daSMS.SelectCommand.Connection = connSMS

        daSMS.InsertCommand = New OleDbCommand("INSERT INTO tblSMS(fldSMSID, fldSMSRecipients, fldSMSMessage) VALUES(@fldSMSID,@fldSMSRecipients,@fldSMSMessage)")
        daSMS.InsertCommand.Connection = connSMS
        daSMS.InsertCommand.Parameters.Add("@fldSMSID", OleDbType.Numeric, 6, "fldSMSID")
        daSMS.InsertCommand.Parameters.Add("@fldSMSRecipients", OleDbType.VarChar, 40, "fldSMSRecipients")
        daSMS.InsertCommand.Parameters.Add("@fldSMSMessage", OleDbType.VarChar, 140, "fldSMSMessage")

        daSMS.Fill(dsSMS)

        'Retrieve from database tblCandidatesRetrieveDataForSMS
        conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        da.SelectCommand = New OleDbCommand("SELECT * FROM tblCandidatesRetrieveDataForSMS")
        da.SelectCommand.Connection = conn

        da.UpdateCommand = New OleDbCommand("UPDATE tblCandidatesRetrieveDataForSMS SET fldDataFirstName = @fldDataFirstName, fldDataSurname = @fldDataSurname, fldDataMobile = @fldDataMobile WHERE fldDataID = @fldDataID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        da.UpdateCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        da.UpdateCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")

        da.UpdateCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        da.InsertCommand = New OleDbCommand("INSERT INTO tblCandidatesRetrieveDataForSMS(fldDataID, fldDataFirstName, fldDataSurname, fldDataMobile) VALUES(@fldDataID,@fldDataFirstName,@fldDataSurname,@fldDataMobile)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        da.InsertCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        da.InsertCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")

        da.InsertCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        Else
            MsgBox("No records exist in database")
        End If

        'Retrieve from database tblStaffRetrieveDataForSMS
        connStaff.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        daStaff.SelectCommand = New OleDbCommand("SELECT * FROM tblStaffRetrieveDataForSMS")
        daStaff.SelectCommand.Connection = connStaff

        daStaff.UpdateCommand = New OleDbCommand("UPDATE tblStaffRetrieveDataForSMS SET fldDataFirstName = @fldDataFirstName, fldDataSurname = @fldDataSurname, fldDataMobile = @fldDataMobile WHERE fldDataID = @fldDataID")
        daStaff.UpdateCommand.Connection = connStaff
        daStaff.UpdateCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daStaff.UpdateCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daStaff.UpdateCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")

        daStaff.UpdateCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daStaff.InsertCommand = New OleDbCommand("INSERT INTO tblStaffRetrieveDataForSMS(fldDataID, fldDataFirstName, fldDataSurname, fldDataMobile) VALUES(@fldDataID,@fldDataFirstName,@fldDataSurname,@fldDataMobile)")
        daStaff.InsertCommand.Connection = connStaff
        daStaff.InsertCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daStaff.InsertCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daStaff.InsertCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")

        daStaff.InsertCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daStaff.Fill(dsStaff)

        If dsStaff.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFieldsStaff()
        Else
            MsgBox("No records exist in database")
        End If

    End Sub
    Public Sub SendSMS()
        Dim client As WebClient = New WebClient
        ' Add a user agent header in case the requested URI contains a query.
        client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)")
        client.QueryString.Add("user", "consc198")
        client.QueryString.Add("password", "aXaUbXSXYWPOdQ")
        client.QueryString.Add("api_id", "3396909")
        client.QueryString.Add("to", txtSMSRecipients.Text)
        client.QueryString.Add("text", txtSMSMessage.Text)
        Dim baseurl As String = "http://api.clickatell.com/http/sendmsg"
        Dim data As Stream = client.OpenRead(baseurl)
        Dim reader As StreamReader = New StreamReader(data)
        Dim s As String = reader.ReadToEnd()
        data.Close()
        reader.Close()
        Return
    End Sub
    Public Sub AddNewRecord()
        'Reset form fields
        txtSMSID.Text = ""
        txtSMSRecipients.Text = ""
        txtSMSMessage.Text = ""

        txtSMSRecipients.Focus()

        'Generate Random Reference Number
        Dim intIDLength As Integer  'length of ID
        Dim intCount As Integer     'variable to count to the value of intIDLength
        Dim intAsc As Integer       'random number to choose what the character will be

        Dim strResult As String     'value of the resulting ID

        Dim intNumber As Integer
        Dim intCharacter As Integer 'the resulting character from one cycle

        intIDLength = 6             'length of sms id

        Randomize()

        For intNumber = 1 To 1

            'number of random characters to generate
            For intCount = 1 To intIDLength


                'choose random character type to generate numbers
                intAsc = Int((1 * Rnd()) + 1)

                Select Case intAsc

                    Case 1
                        intCharacter = Int((Asc("9") - Asc("0") + 1) * Rnd() + Asc("0"))

                End Select

                'concatenate the resulting character to the previously generated character
                strResult = strResult & Chr(intCharacter)

                'repeat process if the value of "intCount" has not reached "intNumberDigits"
            Next intCount

            strResult = strResult

        Next intNumber

        'display the resulting id in the label
        txtSMSID.Text = strResult
    End Sub
    Private Sub btnSendSMS_Click(sender As Object, e As EventArgs) Handles btnSendSMS.Click
        'Validate that all fields have been filled in correctly
        If IsNumeric(txtSMSRecipients.Text) = False Or txtSMSRecipients.Text.Length > 11 Then
            MsgBox("Please enter a valid SMS recipient")
            txtSMSRecipients.Focus()
        ElseIf txtSMSRecipients.Text = "" Or txtSMSMessage.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else
            'Database Operations: Add all data to Access Database
            Dim drSMS As DataRow

            drSMS = dsSMS.Tables(0).NewRow()  'Gets a reference to a new row.
            drSMS("fldSMSID") = txtSMSID.Text

            drSMS("fldSMSRecipients") = txtSMSRecipients.Text
            drSMS("fldSMSMessage") = txtSMSMessage.Text

            dsSMS.Tables(0).Rows.Add(drSMS)
            daSMS.Update(dsSMS)
            dsSMS.AcceptChanges()

            'Sends SMS
            SendSMS()

            AddNewRecord()

            MsgBox("SMS sent successfully")

        End If
    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        AddNewRecord()
    End Sub

    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If cmbSelectGroup.Text = "Candidates" Then
            If ds.Tables(0).Rows.Count > 1 Then
                'Since 0 is the first row
                intCurrentIndex = 0
                FillFields()
            ElseIf ds.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the first record.")
            End If
        ElseIf cmbSelectGroup.Text = "Staff" Then
            If dsStaff.Tables(0).Rows.Count > 1 Then
                'Since 0 is the first row
                intCurrentIndexStaff = 0
                FillFieldsStaff()
            ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the first record.")
            End If
        Else
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If cmbSelectGroup.Text = "Candidates" Then
            If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
                intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
                FillFields()
            ElseIf ds.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the first record.")
            End If
        ElseIf cmbSelectGroup.Text = "Staff" Then
            If intCurrentIndexStaff > 0 Then  'We move back only if we're not at the first row.
                intCurrentIndexStaff = intCurrentIndexStaff - 1  'Subtract one from the current index.
                FillFieldsStaff()
            ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the first record.")
            End If
        Else
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If cmbSelectGroup.Text = "Candidates" Then
            If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
                intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
                FillFields()
            ElseIf ds.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the last record.")
            End If
        ElseIf cmbSelectGroup.Text = "Staff" Then
            If intCurrentIndexStaff < dsStaff.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
                intCurrentIndexStaff = intCurrentIndexStaff + 1  'Add one to the current index.
                FillFieldsStaff()
            ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the last record.")
            End If
        Else
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If cmbSelectGroup.Text = "Candidates" Then
            If ds.Tables(0).Rows.Count > 1 Then
                intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
                FillFields()
            ElseIf ds.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the last record.")
            End If
        ElseIf cmbSelectGroup.Text = "Staff" Then
            If dsStaff.Tables(0).Rows.Count > 1 Then
                intCurrentIndexStaff = dsStaff.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
                FillFieldsStaff()
            ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
                MsgBox("No records exist in database")
            Else
                MessageBox.Show("You are already at the last record.")
            End If
        Else
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub txtSMSRecipients_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtSMSRecipients.KeyPress
        Select Case e.KeyChar
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ",", vbBack '// your pre-selected Characters and Backspace.
                e.Handled = False '// allow.
            Case Else
                e.Handled = True '// block.

                MsgBox("You can only enter Numbers in this TextBox")
                txtSMSRecipients.Focus()
        End Select
    End Sub

    Private Sub cmbSelectGroup_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbSelectGroup.SelectedIndexChanged
        If cmbSelectGroup.Text = "Candidates" Then
            'Empty fields
            txtID.Text = ""
            txtFirstName.Text = ""
            txtSurname.Text = ""
            txtMobile.Text = ""
            'Fill fields
            FillFields()
        ElseIf cmbSelectGroup.Text = "Staff" Then
            'Empty fields
            txtID.Text = ""
            txtFirstName.Text = ""
            txtSurname.Text = ""
            txtMobile.Text = ""
            FillFieldsStaff()
        ElseIf cmbSelectGroup.SelectedIndex = -1 Then
            MsgBox("You have not selected a group")
        Else
            MsgBox("No records exist in database")
        End If
    End Sub
End Class